package com.hxwm;

import com.hxwm.entity.*;
import com.hxwm.service.GenerateScriptService;
import com.hxwm.util.HwLog;
import com.hxwm.util.Util;
import javafx.application.Application;
import javafx.application.Platform;
import javafx.scene.Scene;
import javafx.stage.Stage;
import javafx.stage.FileChooser;
import javafx.scene.control.Alert;
import javafx.scene.control.*;
import javafx.scene.layout.*;
import javafx.stage.DirectoryChooser;
import javafx.geometry.Pos;

import java.sql.*;


import java.io.*;
import java.util.*;

public class DataMigrationApp extends Application {

    private DateSource oldDateSource1;
    private DateSource oldDateSource2;
    private DateSource newDateSource;
    private TextArea logTextArea;  // 添加日志输出框
    private TextArea logTextAreaTab2;  // 添加日志输出框
    public static void main(String[] args) {
        launch(args);
    }

    HwLog hwLog = new HwLog();
    @Override
    public void start(Stage primaryStage) {
        primaryStage.setTitle("数据迁移工具");

        /**
         * 创建数据导出 Tab页 UI
         */
        Button configureOldDatabaseButton = new Button("配置旧数据库源1");
        Button configureNewDatabaseButton = new Button("配置旧数据库源2");

        HBox dataSourceButtons = new HBox(10);
        dataSourceButtons.setAlignment(Pos.CENTER);  // 居中对齐
        dataSourceButtons.getChildren().addAll(configureOldDatabaseButton, configureNewDatabaseButton);

        Button chooseMappingFileButton = new Button("选择映射文件");
        TextField mappingFilePathTextField = new TextField();
        mappingFilePathTextField.setMinWidth(300);

        HBox mappingFileRow = new HBox(10);
        mappingFileRow.getChildren().addAll(chooseMappingFileButton, mappingFilePathTextField);

        Button chooseOutputDirectoryButton = new Button("选择输出目录");
        TextField outputDirectoryTextField = new TextField();
        outputDirectoryTextField.setMinWidth(300);

        HBox outputDirectoryRow = new HBox(10);
        outputDirectoryRow.getChildren().addAll(chooseOutputDirectoryButton, outputDirectoryTextField);

        Button generateScriptButton = new Button("生成Sql脚本");
        Button clearLogButton = new Button("清空日志");

        // 创建日志输出框
        logTextArea = new TextArea();
        logTextArea.setEditable(false);
        logTextArea.setWrapText(true);
        logTextArea.setStyle("-fx-background-color: white; -fx-text-fill: black;-fx-control-inner-background: white;");

        // 添加垂直滚动条
        ScrollPane scrollPane = new ScrollPane(logTextArea);
        scrollPane.setFitToWidth(true);

        // 设置按钮点击事件
        //选择映射文件
        chooseMappingFileButton.setOnAction(e -> {
            FileChooser fileChooser = new FileChooser();
            fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("Excel Files", "*.xlsx"));
            File file = fileChooser.showOpenDialog(primaryStage);
            if(file!=null) {
                mappingFilePathTextField.setText(file.getAbsolutePath());
            }
        });

        //选择输出目录
        chooseOutputDirectoryButton.setOnAction(e -> {
            DirectoryChooser directoryChooser = new DirectoryChooser();
            File file = directoryChooser.showDialog(primaryStage);
            if(file!=null) {
                outputDirectoryTextField.setText(file.getAbsolutePath());
            }
        });

        //清空日志
        clearLogButton.setOnAction(e -> {
            hwLog.setLogInfo("");
            logTextArea.clear();
        });

        //生成sql脚本
        generateScriptButton.setOnAction(e -> {
            String mappingFilePath = mappingFilePathTextField.getText();
            String outputDirectory = outputDirectoryTextField.getText();
            generateScript(mappingFilePath,outputDirectory);
        });

        //配置旧数据源
        configureOldDatabaseButton.setOnAction(e -> {
            showDatabaseConfigDialog("配置旧数据库源","old1");
        });

        //配置新数据源
        configureNewDatabaseButton.setOnAction(e -> {
            showDatabaseConfigDialog("配置新数据库源","old1");
        });
        // 创建界面布局
        VBox vbox = new VBox(10);
        vbox.setAlignment(Pos.CENTER);
        vbox.getChildren().addAll(dataSourceButtons, mappingFileRow, outputDirectoryRow, generateScriptButton, scrollPane,clearLogButton);

        // 创建TabPane
        TabPane tabPane = new TabPane();
        Tab tab1 = new Tab("数据导出");
        tab1.setContent(vbox);  // vbox是你原来的布局
        tab1.setClosable(false);  // 将Tab设置为不可关闭

        /**
         * ====================================================================================================================================
         * 创建数据导入Tab页UI
         */
        // 添加数据库源配置按钮
        Button configureDatabaseButton = new Button("配置数据库源");
        VBox configureBox = new VBox(10, configureDatabaseButton);
        configureBox.setAlignment(Pos.CENTER);

        // 添加选择导入脚本按钮和文本框
        Button chooseImportScriptButton = new Button("选择导入脚本");
        TextField importScriptTextField = new TextField();
        importScriptTextField.setMinWidth(300);
        HBox importScriptRow = new HBox(10);
        importScriptRow.getChildren().addAll(chooseImportScriptButton, importScriptTextField);

        //选择导出脚本的目录
        Button chooseOutputLogDirectoryButton = new Button("选择输出目录");
        TextField outputLogDirectoryTextField = new TextField();
        outputLogDirectoryTextField.setMinWidth(300);
        HBox outputLogDirectoryRow = new HBox(10);
        outputLogDirectoryRow.getChildren().addAll(chooseOutputLogDirectoryButton, outputLogDirectoryTextField);

        // 添加数据库编码选择下拉框
        Label encodingLabel = new Label("选择数据库编码：");
        ComboBox<String> encodingComboBox = new ComboBox<>();
        encodingComboBox.getItems().addAll("UTF-8", "GBK", "ISO-8859-1");  // 添加编码集选项
        encodingComboBox.setValue("UTF-8");  // 设置默认值
        HBox encodingRow = new HBox(10);
        encodingRow.getChildren().addAll(encodingLabel, encodingComboBox);

        // 添加错误处理单选按钮
        Label errorHandlingLabel = new Label("遇到错误时是否跳过：");
        ToggleGroup errorHandlingToggleGroup = new ToggleGroup();
        RadioButton skipOnErrorRadioButton = new RadioButton("是");
        skipOnErrorRadioButton.setToggleGroup(errorHandlingToggleGroup);
        skipOnErrorRadioButton.setSelected(true);  // 默认选中
        RadioButton stopOnErrorRadioButton = new RadioButton("否");
        stopOnErrorRadioButton.setToggleGroup(errorHandlingToggleGroup);
        HBox errorHandlingRow = new HBox(10);
        errorHandlingRow.getChildren().addAll(errorHandlingLabel, skipOnErrorRadioButton, stopOnErrorRadioButton);

        //执行按钮
        Button startImportButton = new Button("开始");
        Button pauseButton = new Button("暂停");
        HBox buttonRow = new HBox(10);
        buttonRow.setAlignment(Pos.CENTER);  // 居中对齐
        buttonRow.getChildren().addAll(startImportButton, pauseButton);

        // 添加日志输出框
        logTextAreaTab2 = new TextArea();
        logTextAreaTab2.setEditable(false);
        logTextAreaTab2.setWrapText(true);
        logTextAreaTab2.setStyle("-fx-background-color: white; -fx-text-fill: black;");
        ScrollPane scrollPaneTab2 = new ScrollPane(logTextAreaTab2);
        scrollPaneTab2.setFitToWidth(true);

        Button clearImportLogButton = new Button("清除日志");

        // 创建Tab 2 的布局
        VBox tab2Layout = new VBox(10);
        tab2Layout.setAlignment(Pos.CENTER);
        tab2Layout.getChildren().addAll(configureBox, importScriptRow, outputLogDirectoryRow,encodingRow, errorHandlingRow,buttonRow,scrollPaneTab2,clearImportLogButton);

        // 创建空的Tab，你可以在后续添加内容
        Tab tab2 = new Tab("数据导入");
        tab2.setContent(tab2Layout);  // 空的VBox
        tab2.setClosable(false);  // 将Tab设置为不可关闭

        // 设置按钮点击事件
        configureDatabaseButton.setOnAction(e -> {
            showDatabaseConfigDialog("配置数据库源", "new");  // 配置数据库源的方法
        });
        //开始导入按钮事件
        startImportButton.setOnAction(e -> {
            String scriptFilePath = importScriptTextField.getText();
            String logDirPath = outputLogDirectoryTextField.getText();

            try {
                executeScript(scriptFilePath,logDirPath);
            } catch (Exception ex) {
                log(logTextAreaTab2,ex.getMessage());
            }
        });
        //清空日志
        clearImportLogButton.setOnAction(e -> {
            logTextAreaTab2.clear();
        });

        //选择脚本
        chooseImportScriptButton.setOnAction(e -> {
            FileChooser fileChooser = new FileChooser();
            fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("SQL Files", "*.sql"));
            File file = fileChooser.showOpenDialog(primaryStage);
            if (file != null) {
                importScriptTextField.setText(file.getAbsolutePath());
            }
        });

        //选择日志输出目录
        chooseOutputLogDirectoryButton.setOnAction(e->{
            DirectoryChooser directoryChooser = new DirectoryChooser();
            File file = directoryChooser.showDialog(primaryStage);
            if(file!=null) {
                outputLogDirectoryTextField.setText(file.getAbsolutePath());
            }
        });

        // 将Tab添加到TabPane中
        tabPane.getTabs().addAll(tab1, tab2);

        Scene scene = new Scene(tabPane, 500, 450);
        primaryStage.setScene(scene);
        primaryStage.show();
    }

    private void executeScript(String scriptFilePath,String outLogDirPath) throws Exception{

        if(newDateSource == null || scriptFilePath == null){
            showInformationDialog("提示", "脚本文件、数据源配置不能为空！");
        }
        try (BufferedReader reader = new BufferedReader(new FileReader(scriptFilePath))) {
            // 获取数据库连接
            try (Statement statement = newDateSource.getConnection().createStatement()) {
                //处理sql脚本的时候，不要将全部文件加载到内存，而是逐行读取，按流式处理
                String line;
                int i = 0;
                while((line = reader.readLine()) != null){
                    if(!line.trim().isEmpty()){
                        try{
                            log(logTextAreaTab2,"进度：" + i++);
                            statement.execute(line);
                        }catch (SQLException e){
                            writeErrorSQLToFile(line,outLogDirPath);
                        }
                    }
                }
            }
        } catch (IOException e) {
            throw new Exception("读取脚本文件失败：" + e.getMessage());
        }
    }
    private void writeErrorSQLToFile(String errorSQL,String outLogDirPath) {
        try (PrintWriter writer = new PrintWriter(new FileWriter(outLogDirPath + File.separator +"error_log.txt", true))) {
            writer.println(errorSQL);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     *
     * @param mappingFilePath
     * @param outputDirectory
     */
    private void generateScript(String mappingFilePath,String outputDirectory){

        if (mappingFilePath.isEmpty() || outputDirectory.isEmpty() || (oldDateSource1 == null && oldDateSource2 == null)) {
            // 显示错误消息或警告
            showInformationDialog("提示", "映射文件、输出路径、旧数据源配置不能为空！");
            return;
        }
        try {
            //获取数据库连接
            Connection oldDbConnection1 = null;
            Connection oldDbConnection2 = null;
            Statement statement1 = null;
            Statement statement2 = null;
            if(oldDateSource1 != null){
                oldDbConnection1 = oldDateSource1.getConnection();
                statement1 = oldDbConnection1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            }
            if(oldDateSource2 != null){
                oldDbConnection2 = oldDateSource2.getConnection();
                statement2 = oldDbConnection2.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            }
            //读取映射文件解析新旧表字段
            MapperExcel mapperExcel = new MapperExcel(mappingFilePath);
            List<MapperSheet> mapperSheetList = mapperExcel.getMapperSheetList();//获取mapperSheetList

            log(logTextArea,"开始生成SQL脚本=======================================");
            //文件生成
            for (MapperSheet mapperSheet : mapperSheetList) {
                hwLog = new HwLog();
                if(mapperSheet.isMultiTable()){
                    if(statement1 != null && statement2 != null){//数据来源不同库
                        GenerateScriptService.generateMultiSqlFile(statement1,statement2,mapperSheet,outputDirectory,hwLog);
                    }else{
                        GenerateScriptService.generateMultiSqlFile(statement1,mapperSheet,outputDirectory,hwLog);
                    }
                }else{
                    GenerateScriptService.generateSqlFile(statement1,mapperSheet,outputDirectory,hwLog);
                }
                log(logTextArea,hwLog.getLogInfo());
            }
            log(logTextArea,"生成SQL脚本结束=======================================");

            //关闭连接
            if(statement1 != null){
                statement1.close();
            }
            if(oldDbConnection1 != null) {
                oldDbConnection1.close();
            }
            if(statement2 != null){
                statement2.close();
            }
            if(oldDbConnection2 != null) {
                oldDbConnection2.close();
            }
            // 提示成功生成SQL脚本
            String successMessage = "SQL脚本生成成功：";
            showInformationDialog("成功", successMessage);

        }catch (Exception exception){
            exception.printStackTrace();
            // 处理异常情况
            showInformationDialog("失败", exception.getMessage());
        }
    }
    /**
     * 添加日志输出方法
     * @param message
     */
    private void log(TextArea logTextArea,String message) {
        //Platform.runLater(() -> logTextArea.appendText(message + "\n"));
        logTextArea.appendText(message + "\n");
    }
    /**
     * 提示框
     * @param title
     * @param message
     */
    private void showInformationDialog(String title, String message) {
        Alert alert = new Alert(Alert.AlertType.INFORMATION);
        alert.setTitle(title);
        alert.setHeaderText(null);
        alert.setContentText(message);
        alert.showAndWait();
    }

    /**
     * 数据库配置弹框
     * @param title
     * @param type
     */
    private void showDatabaseConfigDialog(String title,String type) {
        DialogPane dialogPane = new DialogPane();
        dialogPane.getButtonTypes().addAll(ButtonType.OK, ButtonType.CANCEL);

        GridPane gridPane = new GridPane();
        gridPane.setHgap(10);
        gridPane.setVgap(10);
        gridPane.setAlignment(Pos.CENTER);

        TextField urlField = new TextField();
        TextField userField = new TextField();
        PasswordField passwordField = new PasswordField();

        if("old1".equals(type)){
            if(oldDateSource1!=null){
                urlField.setText(oldDateSource1.getUrl());
                userField.setText(oldDateSource1.getUser());
                passwordField.setText(oldDateSource1.getPassword());
            }
        }else if("old2".equals(type)){
            if(oldDateSource2!=null){
                urlField.setText(oldDateSource2.getUrl());
                userField.setText(oldDateSource2.getUser());
                passwordField.setText(oldDateSource2.getPassword());
            }
        }else if("new".equals(type)){
            if(newDateSource!=null){
                urlField.setText(newDateSource.getUrl());
                userField.setText(newDateSource.getUser());
                passwordField.setText(newDateSource.getPassword());
            }
        }

        gridPane.add(new Label("数据库URL:"), 0, 0);
        gridPane.add(urlField, 1, 0);
        gridPane.add(new Label("用户名:"), 0, 1);
        gridPane.add(userField, 1, 1);
        gridPane.add(new Label("密码:"), 0, 2);
        gridPane.add(passwordField, 1, 2);

        Button testButton = new Button("测试连接");
        testButton.setOnAction(event -> testDatabaseConnection(urlField.getText(), userField.getText(), passwordField.getText()));
        gridPane.add(testButton, 1, 3);

        dialogPane.setHeaderText(title);
        dialogPane.setContent(gridPane);

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.setDialogPane(dialogPane);

        dialog.setResultConverter(buttonType -> {
            if (buttonType == ButtonType.OK) {
                String url = urlField.getText();
                String user = userField.getText();
                String password = passwordField.getText();

                if("old1".equals(type)){
                    oldDateSource1 = new DateSource(url,user,password);
                }else if("old2".equals(type)){
                    oldDateSource2 = new DateSource(url,user,password);
                }else if("new".equals(type)){
                    newDateSource = new DateSource(url,user,password);
                }
            }
            return null;
        });

        dialog.setTitle(title);
        dialog.showAndWait();
    }

    /**
     * 数据库连接测试
     * @param url
     * @param user
     * @param password
     */
    private void testDatabaseConnection(String url, String user, String password) {
        try {
            String dbType = url.split(":")[1];
            if("db2".equals(dbType)) {
                Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            }else if("mysql".equals(dbType)){
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            }else if("oracle".equals(dbType)){
                Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            }
            // 尝试连接数据库
            Connection connection = DriverManager.getConnection(url, user, password);
            // 如果成功，显示连接成功的消息
            showInformationDialog("连接测试","连接成功");
            // 关闭连接
            connection.close();
        } catch (Exception ex) {
            // 如果失败，显示错误消息
            showInformationDialog("连接测试","连接失败");
        }
    }

}
